package com.ffms.dao.impl;

import com.ffms.entity.FamilyMember;
import com.ffms.entity.Shouru;
import com.ffms.entity.ZhiChu;
import com.ffms.util.JDBCUtils;

import java.sql.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class ZhiChuDaoImpl {
    public List<ZhiChu> zhuchu() {
        List<ZhiChu> shouruList = new ArrayList<ZhiChu>();
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;

        try {
            conn = JDBCUtils.getConnection();
            //2.定义sql
            String sql = "select tm.*,sh.* from t_zhichu sh join tables.t_member tm on tm.sid = sh.mzsid where 1=1";
            //3.获取执行sql的对象)
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);
            while (rs.next()) {

                int zhichuid = rs.getInt("zhichuid");
                String why = rs.getString("why");
                String money = rs.getString("money");
                Date date = rs.getDate("date");
                String way = rs.getString("way");

                String name = rs.getString("name");
                int sid = rs.getInt("sid");
                FamilyMember fm = new FamilyMember(sid, name);
                ZhiChu zhichu = new ZhiChu(zhichuid, why, money, date, way, fm);
//                将对象放入集合
                shouruList.add(zhichu);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                conn.close();
                stmt.close();
                rs.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return shouruList;
    }

    public void deletZhiChuById(String id) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        int n = 0;
        try {
            conn = JDBCUtils.getConnection();
            //2.定义sql
            String sql = "delete from t_zhichu where zhichuid=?";
            //3.获取执行sql的对象
            pstmt = conn.prepareStatement(sql);
            Object[] prams = {id};
            for (int i = 0; i < prams.length; i++) {
                pstmt.setObject(i + 1, prams[i]);
            }
            n = pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public int updateZhiCHu(ZhiChu zhiChu) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        int n = 0;
        try {
            conn = JDBCUtils.getConnection();
            //2.定义sql
            String sql = "update t_zhichu set `why` =? ,money = ?, way = ? where zhichuid=? ";
            //3.获取执行sql的对象
            pstmt = conn.prepareStatement(sql);
            Object[] prams = {zhiChu.getWhy(), zhiChu.getMoney(), zhiChu.getWay(), zhiChu.getId()};
            for (int i = 0; i < prams.length; i++) {
                pstmt.setObject(i + 1, prams[i]);
            }
            n = pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return n;

    }

    public ZhiChu getById(int id) {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        ZhiChu zhichu = null;

        try {
            conn = JDBCUtils.getConnection();
            //2.定义sql
            String sql = "select tm.*,sh.*  from t_zhichu sh join tables.t_member tm on tm.sid = sh.mzsid where  zhichuid =" + id;
            //3.获取执行sql的对象
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);
            if (rs.next()) {
                int zhichuid = rs.getInt("zhichuid");
                String why = rs.getString("why");
                String money = rs.getString("money");
                Date date = rs.getDate("date");
                String way = rs.getString("way");
                String name = rs.getString("name");
                int sid = rs.getInt("sid");
                FamilyMember fm = new FamilyMember(sid, name);
                zhichu = new ZhiChu(zhichuid, why, money, date, way, fm);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                conn.close();
                stmt.close();
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return zhichu;
    }

    public List<ZhiChu> zhuchuByMid(int thisSid) {
        List<ZhiChu> shouruList = new ArrayList<ZhiChu>();
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;

        try {
            conn = JDBCUtils.getConnection();
            //2.定义sql
            String sql = "select tm.*,sh.* from t_zhichu sh join tables.t_member tm on tm.sid = sh.mzsid where sh.mzsid=" + thisSid;
            //3.获取执行sql的对象)
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);
            while (rs.next()) {

                int zhichuid = rs.getInt("zhichuid");
                String why = rs.getString("why");
                String money = rs.getString("money");
                Date date = rs.getDate("date");
                String way = rs.getString("way");

                String name = rs.getString("name");
                int sid = rs.getInt("sid");
                FamilyMember fm = new FamilyMember(sid, name);
                ZhiChu zhichu = new ZhiChu(zhichuid, why, money, date, way, fm);
//                将对象放入集合
                shouruList.add(zhichu);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                conn.close();
                stmt.close();
                rs.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return shouruList;
    }

    public List<ZhiChu> tj() {
        List<ZhiChu> zhiChuArrayList = new ArrayList<ZhiChu>();
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;

        try {
            conn = JDBCUtils.getConnection();
            //2.定义sql
            String sql = "select mzsid, sum(money) a from t_zhichu group by t_zhichu.mzsid";
            //3.获取执行sql的对象)
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);
            while (rs.next()) {

                int mzsid = rs.getInt("mzsid");

                String money = rs.getString("a");
                ZhiChu zhichu = new ZhiChu(mzsid, money);
//                将对象放入集合
                zhiChuArrayList.add(zhichu);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                conn.close();
                stmt.close();
                rs.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return zhiChuArrayList;
    }


    public int addZhichu(ZhiChu zhichu) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        int n = 0;
        try {
            conn = JDBCUtils.getConnection();
            //2.定义sql
            String sql = "insert into tables.t_zhichu values (zhichuid,?,?,?,?,?)";
            //3.获取执行sql的对象
            pstmt = conn.prepareStatement(sql);

            Object[] prams = {zhichu.getWhy(), zhichu.getMoney(),zhichu.getDate(),zhichu.getWay(),zhichu.getMzsid()};
            for (int i = 0; i < prams.length; i++) {
                pstmt.setObject(i + 1, prams[i]);

            }
            n = pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return n;
    }

}
